const Service = require('egg').Service
const dayjs = require('dayjs')
const citys = require('../data/city')
const town = require('../data/town')

class ScreenService extends Service {
  // 场所分类扫码数据
  async placeTypeScanByScope(...args) {
    const { moss, knex } = this.app
    const { dict } = this.config
    const { TABLE_SCAN_RECORD } = this.config.constant

    const { startTime, endTime, label, scope } = this.ctx.helper.getDateOptions(...args)

    let total = 0
    const counts = [`count(*) as total`, `cstype as placeType`]
    const data = await knex(TABLE_SCAN_RECORD)
      .select(knex.raw(counts.join(',')))
      .where('scancode_date', '>=', startTime)
      .where('scancode_date', '<=', endTime)
      .groupBy('placeType')
      .orderBy('total', 'desc')
      .then(res => {
        return res.map(item => {
          total += item.total

          if (item.placeType < 10) item.placeType = '0' + item.placeType

          return {
            value: item.total,
            label: dict.placeTypeValues[item.placeType] || `${item.placeType}未知`,
          }
        })
      })

    await this.setData({
      trackScope: scope,
      trackName: scope + '_place_type_scan',
      trackTitle: label + '场所分类扫码数据',
      trackDate: startTime,
      trackData: data,
      total,
    })

    return data
  }

  // 场所分类数量
  async placeType() {
    const { moss, knex } = this.app
    const { dict } = this.config
    const { TABLE_PLACE_CODE } = this.config.constant

    let total = 0
    const counts = [`count(*) as total`, `unit_type as placeType`]
    const data = await knex(TABLE_PLACE_CODE)
      .select(knex.raw(counts.join(',')))
      .groupBy('placeType')
      .orderBy('total', 'desc')
      .then(res => {
        return res.map(item => {
          total += item.total

          return {
            value: item.total,
            label: dict.placeTypeValues[item.placeType] || `${item.placeType}未知`,
          }
        })
      })

    await this.setData({
      trackScope: 'all',
      trackName: 'place_type',
      trackTitle: '场所分类数量',
      trackData: data,
      total,
    })

    return data
  }

  /**
   * 人员流入(来/返人员)
   * @param  date 日期
   * @param  scope 范围 week/month
   * @returns
   */
  async personEnterByScope(...args) {
    const { moss, knex } = this.app
    const { dict } = this.config
    const { TABLE_PERSON_BACK_HOME } = this.config.constant

    const { startTime, endTime, label, scope } = this.ctx.helper.getDateOptions(...args)

    const total = await knex(TABLE_PERSON_BACK_HOME)
      .where('entry_time', '>=', startTime)
      .where('entry_time', '<=', endTime)
      .count()
      .then(res => {
        return res[0]['count(*)']
      })

    await this.setData({
      trackScope: scope,
      trackName: scope + '_person_enter',
      trackTitle: label + '人员流入',
      trackDate: startTime,
      total,
    })

    return total
  }

  /**
   * 按时间范围获取乡镇核酸结果
   * @param  [scope=day] 范围 day/week/month
   * @param  [date=今天] 日期
   */
  async townHsResultByScope(...args) {
    const data = await this.service.hs.townHsResultByScope(...args)
    const { startTime, scope, label } = this.ctx.helper.getDateOptions(...args)

    await this.setData({
      trackScope: scope,
      trackName: scope + '_town_hs_result',
      trackTitle: label + '乡镇核酸结果',
      trackDate: startTime,
      trackData: data,
    })

    return data
  }

  // 获取乡镇核酸采集
  async townHsCollect() {
    const data = await this.service.hs.townHsCollect()

    await this.setData({
      trackScope: 'all',
      trackName: 'hs_collect',
      trackTitle: '乡镇核酸采集',
      trackData: data,
    })

    return data
  }

  // async townHsResult() {
  //   const data = await this.service.hs.townHsResult()

  //   await this.setData({
  //     trackScope: 'all',
  //     trackName: 'hs_result',
  //     trackTitle: '乡镇核酸结果',
  //     trackDate: startTime,
  //     trackData: data,
  //   })

  //   return data
  // }

  // 乡镇常住人口
  async townPerson() {
    const { knex } = this.app
    const { TABLE_PERSON, TABLE_PERSON_BACK_HOME, TABLE_OUT } = this.config.constant
    const { helper } = this.ctx
    const { prefix } = this.config

    const data = town.map(townName => {
      return {
        label: townName,
        total: 0,
        cz: 0,
        fcz: 0,
      }
    })

    await helper.queue(data, async (item, index) => {
      const townName = item.label

      if (prefix == 'xhx') {
        await knex(TABLE_PERSON)
          .count('*', { as: 'cz' })
          .where('areajd', townName)
          .then(res => Object.assign(item, res[0]))

        await knex(TABLE_OUT)
          .count('*', { as: 'fcz' })
          .where('areajd', townName)
          .then(res => Object.assign(item, res[0]))

        item.total = item.cz + item.fcz
      } else {
        await knex(TABLE_PERSON)
          .select(
            knex.raw(
              [
                `count(*) AS total`,
                `count(sfczrq = '是' or null) AS cz`,
                `count(sfczrq = '否' or null) AS fcz`,
              ].join(',')
            )
          )
          .where('areajd', townName)
          .then(res => {
            for (const key in res[0]) {
              item[key] = res[0][key]
            }
          })
      }

      item.percent = Number(((item.cz / item.total) * 100).toFixed(2))
    })

    await this.setData({
      trackScope: 'all',
      trackName: 'town_person',
      trackTitle: '乡镇常住人口',
      trackData: data,
    })

    return data
  }

  // 乡镇阳性数据
  async townPositive(date = dayjs().format('YYYY-MM-DD')) {
    const { knex } = this.app
    const { TABLE_POSITIVE } = this.config.constant

    const counts = [
      `count(*) AS total`,
      `count(sfjzgl = '1' or null) AS jzgl`,
      `count(sfjzgl = '2' or null) AS jjgl`,
      `count(sfjzgl = '3' or null) AS shsc`,
      `count(sfjzgl = '4' or null) AS zdjz`,
      `count(sfzdry = '是' or null) AS zdry`,
      `count(qztime = '${date}' or null) AS new`,
      `areajd as label`,
    ]

    let total = 0
    let jzgl = 0
    let jjgl = 0
    let shsc = 0
    let zdjz = 0
    let zdry = 0
    const list = await knex(TABLE_POSITIVE)
      .select(knex.raw(counts.join(',')))
      // .where('sfczrq', '是')
      .groupBy('label')
      .orderBy('total', 'desc')
      .then(res => {
        return res.map(item => {
          total += item.total
          jzgl += item.jzgl
          jjgl += item.jjgl
          shsc += item.shsc
          zdjz += item.zdjz
          zdry += item.zdry
          return item
        })
      })

    const data = {
      list: list,
      jzgl,
      jjgl,
      shsc,
      zdjz,
      zdry,
    }

    await this.setData({
      trackScope: 'all',
      trackName: 'town_positive',
      trackTitle: '乡镇隔离数据',
      trackData: data,
      trackDate: date,
      total,
    })

    return data
  }

  /**
   * 按范围获取场所扫码数据
   * @param  date 日期
   * @param  scope 范围 day/week/month
   */
  async placeScanByScope(...args) {
    const { moss, knex } = this.app
    const { dict } = this.config
    const { TABLE_SCAN_RECORD } = this.config.constant

    const { startTime, endTime, label, scope } = this.ctx.helper.getDateOptions(...args)

    let total = 0

    const counts = [
      `count(*) as total`,
      `count(healthcode_status = '红码' or null) AS redCode`,
      `count(healthcode_status = '黄码' or null) AS yellowCode`,
      `count(healthcode_status = '绿码' or null) AS greenCode`,
      `place_name as label`,
      'any_value(cstype) as placeType',
    ]
    const data = await knex(TABLE_SCAN_RECORD)
      .select(knex.raw(counts.join(',')))
      .where('scancode_date', '>=', startTime)
      .where('scancode_date', '<=', endTime)
      .groupBy('label')
      .orderBy('total', 'desc')
      .then(res => {
        return res.map(item => {
          total += item.total

          if (item.placeType < 10) item.placeType = '0' + item.placeType

          return {
            ...item,
            placeTypeName: dict.placeTypeValues[item.placeType] || `${item.placeType}未知`,
          }
        })
      })

    await this.setData({
      trackScope: scope,
      trackName: scope + '_place_scan',
      trackTitle: label + '场所扫码数据',
      trackDate: startTime,
      trackData: data,
      total,
    })

    return data
  }

  /**
   * 按范围获取场所扫码人员年龄
   * @param  date 日期
   * @param  scope 范围 day/week/month
   */
  async placeScnPersonAgeByScope(...args) {
    const { moss, knex } = this.app
    const { dict } = this.config
    const { TABLE_SCAN_RECORD } = this.config.constant

    const { startTime, endTime, label, scope } = this.ctx.helper.getDateOptions(...args)

    let total = 0

    const counts = [
      `count(*) as total`,
      `(substring(now(),1,4)-substring(card_no,7,4))-(substring(card_no,11,4)-date_format(now(),'%m%d')>0) as age`,
    ]

    // 年龄段
    const ageGroup = {
      '0-20岁': 0,
      '20-40岁': 0,
      '40-60岁': 0,
      '60岁以上': 0,
    }

    // 获取年龄对应的年龄段
    const getAgeKey = age => {
      let key
      if (age >= 0 && age < 20) {
        key = '0-20岁'
      } else if (age >= 20 && age < 40) {
        key = '20-40岁'
      } else if (age >= 40 && age < 60) {
        key = '40-60岁'
      } else {
        key = '60岁以上'
      }
      return key
    }

    // 按年龄分组
    await knex(TABLE_SCAN_RECORD)
      .select(knex.raw(counts.join(',')))
      .where('scancode_date', '>=', startTime)
      .where('scancode_date', '<=', endTime)
      .groupBy('age')
      .orderBy('total', 'desc')
      .then(res => {
        return res.map(item => {
          total += item.total
          const ageKey = getAgeKey(item.age)
          ageGroup[ageKey] += item.total

          return item
        })
      })

    await this.setData({
      trackScope: scope,
      trackName: scope + '_place_scan_age_group',
      trackTitle: label + '场所扫码年龄段',
      trackDate: startTime,
      trackData: Object.keys(ageGroup).map(key => {
        return {
          label: key,
          value: ageGroup[key],
        }
      }),
      total,
    })

    return ageGroup
  }

  /**
   * 按范围获取乡镇扫码数据
   * @param  date 日期
   * @param  scope 范围 day/week/month
   */
  async townScanByScope(...args) {
    const { knex } = this.app
    const { TABLE_SCAN_RECORD } = this.config.constant
    const { helper } = this.ctx

    const { startTime, endTime, label, scope } = helper.getDateOptions(...args)

    const data = town.map(townName => {
      return {
        label: townName,
        total: 0,
      }
    })

    await helper.queue(data, async (item, index) => {
      const townName = item.label

      await knex(TABLE_SCAN_RECORD)
        .where('areajd', townName)
        .where('scancode_date', '>=', startTime)
        .where('scancode_date', '<=', endTime)
        .count('*', { as: 'total' })
        .then(res => Object.assign(item, res[0]))
    })

    // let total = 0
    // const counts = [`count(*) as value`, `areajd as label`]
    // const data = await knex(TABLE_SCAN_RECORD)
    //   .select(knex.raw(counts.join(',')))
    //   .where('scancode_date', '>=', startTime)
    //   .where('scancode_date', '<=', endTime)
    //   .groupBy('label')
    //   .orderBy('value', 'desc')
    //   .then(res => {
    //     return res.map(item => {
    //       total += item.value
    //       return item
    //     })
    //   })

    await this.setData({
      trackScope: scope,
      trackName: scope + '_town_scan',
      trackTitle: label + '乡镇扫码数据',
      trackDate: startTime,
      trackData: data,
    })

    return data
  }

  // 获取乡镇场所码数量
  async townPlaceCode(...args) {
    const { moss, knex } = this.app
    const { dict } = this.config
    const { TABLE_PLACE_CODE } = this.config.constant

    let total = 0
    const counts = [`count(*) as value`, `unit_areajd as label`]
    const data = await knex(TABLE_PLACE_CODE)
      .select(knex.raw(counts.join(',')))
      .groupBy('label')
      .orderBy('value', 'desc')
      .then(res => {
        return res.map(item => {
          total += item.value

          return item
        })
      })

    await this.setData({
      trackScope: 'all',
      trackName: 'town_place_code',
      trackTitle: '乡镇场所码',
      trackData: data,
      total,
    })

    return data
  }

  /**
   * 按范围获取全县扫码数据
   * @param  date 日期
   * @param  scope 范围 day/week/month
   */
  async scanNumByScope(...args) {
    const { moss, knex } = this.app
    const { dict } = this.config
    const { TABLE_SCAN_RECORD } = this.config.constant

    const { startTime, endTime, label, scope } = this.ctx.helper.getDateOptions(...args)

    const counts = [`count(*) as total`]
    const total = await knex(TABLE_SCAN_RECORD)
      .select(knex.raw(counts.join(',')))
      .where('scancode_date', '>=', startTime)
      .where('scancode_date', '<=', endTime)
      .then(res => {
        return res[0].total
      })

    await this.setData({
      trackScope: scope,
      trackName: scope + '_scan',
      trackTitle: label + '全县扫码数据',
      trackDate: startTime,
      total,
    })

    return total
  }

  /**
   * 密接人员
   */
  async probablyPerson() {
    const { moss, knex } = this.app
    const { dict } = this.config
    const { TABLE_PROBABLY } = this.config.constant

    let total = 0
    const data = await knex(TABLE_PROBABLY).then(res => {
      return res.map(item => {
        let handle
        if (item.jcjg == '1') {
          handle = '集中隔离'
        } else if (item.jcjg == '2') {
          handle = '居家隔离'
        } else {
          handle = item.jcjg || ''
        }

        console.log('handle', handle)

        return {
          name: item.yxryname,
          address: item.jcareajd,
          relation: item.yyxbrgx,
          handle,
        }
      })
    })

    await this.setData({
      trackScope: 'all',
      trackName: 'person_probably',
      trackTitle: '密接人员',
      trackData: data,
      total,
    })

    return data
  }

  // 设置数据
  async setData(data) {
    const { ctx } = this

    await ctx.model.TrackData.findOneAndReplace(
      {
        trackName: data.trackName,
        trackDate: data.trackDate,
        trackScope: data.trackScope,
      },
      data,
      {
        upsert: true,
        new: true,
      }
    )
  }

  /**
   * 返乡人员来源地
   * @param  date 日期
   * @param  scope 范围 week/month
   * @returns
   */
  async personFromCityByScope(...args) {
    const { knex } = this.app
    const { TABLE_PERSON_BACK_HOME } = this.config.constant
    const { helper } = this.ctx

    const { startTime, endTime, label, scope } = helper.getDateOptions(...args)

    const fromCity = {
      province: [],
      otherProvince: [],
    }

    // const data = town.map(townName => {
    //   return {
    //     label: townName,
    //   }
    // })
    // await helper.queue(data, async (item, index) => {
    //   const townName = item.label

    //   await knex(TABLE_PERSON_BACK_HOME)
    //     .select(
    //       knex.raw(
    //         [
    //           `count(*) AS total`,
    //           `count(previous_area = '河南省' or null) AS sn`,
    //           // `count(previous_area != '河南省' or null) AS sw`,
    //         ].join(',')
    //       )
    //     )
    //     .where('entry_time', '>=', startTime)
    //     .where('entry_time', '<=', endTime)
    //     .where('to_county_code', townName)
    //     .then(res => {
    //       fromCity.province.push({
    //         label: townName,
    //         value: res[0].sn,
    //       })
    //       fromCity.otherProvince.push({
    //         label: townName,
    //         value: res[0].total - res[0].sn, // 省外人数
    //       })
    //     })
    // })

    const getProvince = city => {
      if (citys.includes(city)) {
        return 'province'
      } else {
        return 'otherProvince'
      }
    }
    const counts = [`count(*) as count`, `to_city_code as city`]
    await knex(TABLE_PERSON_BACK_HOME)
      .select(knex.raw(counts.join(',')))
      .where('entry_time', '>=', startTime)
      .where('entry_time', '<=', endTime)
      .groupBy('city')
      .then(res => {
        return res.map(item => {
          const provinceKey = getProvince(item.city)

          fromCity[provinceKey].push({
            label: item.city,
            value: item.count,
          })
        })
      })

    await this.setData({
      trackScope: scope,
      trackName: scope + '_person_from_city',
      trackTitle: label + '返乡人员来源地',
      trackDate: startTime,
      trackData: fromCity,
    })

    return fromCity
  }

  /**
   * 乡镇人员流入(来/返人员)
   * @param  date 日期
   * @param  scope 范围 week/month
   * @returns
   */
  async townPersonEnterByScope(...args) {
    const { knex } = this.app
    const { TABLE_PERSON_BACK_HOME } = this.config.constant
    const { helper } = this.ctx

    const { startTime, endTime, label, scope } = helper.getDateOptions(...args)

    const data = town.map(item => {
      return {
        label: item,
      }
    })

    let total = 0
    await helper.queue(data, async (item, index) => {
      const townName = item.label

      await knex(TABLE_PERSON_BACK_HOME)
        .where('to_county_code', townName)
        .where('entry_time', '>=', startTime)
        .where('entry_time', '<=', endTime)
        .count('*', { as: 'value' })
        .then(res => {
          for (const key in res[0]) {
            item[key] = res[0][key]
          }
        })

      total += item.value
    })

    data.map(item => {
      item.percent = total ? Number(((item.value / total) * 100).toFixed(2)) : 0
    })

    // let total = 0
    // const counts = [`count(*) as value`, `to_county_code as label`]
    // const data = await knex(TABLE_PERSON_BACK_HOME)
    //   .select(knex.raw(counts.join(',')))
    //   .where('entry_time', '>=', startTime)
    //   .where('entry_time', '<=', endTime)
    //   .groupBy('label')
    //   .then(res => {
    //     return res
    //       .map(item => {
    //         total += item.value

    //         return item
    //       })
    //       .map(item => {
    //         return {
    //           ...item,
    //           percent: Number(((item.value / total) * 100).toFixed(2)),
    //         }
    //       })
    //   })

    await this.setData({
      trackScope: scope,
      trackName: scope + '_town_person_enter',
      trackTitle: label + '人员流入',
      trackDate: startTime,
      trackData: data,
    })

    return data
  }

  /**
   * 人员年龄段分布
   */
  async personAge() {
    const { moss, knex } = this.app
    const { dict } = this.config
    const { TABLE_PERSON } = this.config.constant

    let total = 0

    const counts = [`count(*) as total`, `age`]

    // 年龄段
    const ageGroup = {
      '0-20岁': 0,
      '20-40岁': 0,
      '40-60岁': 0,
      '60岁以上': 0,
    }

    // 获取年龄对应的年龄段
    const getAgeKey = age => {
      let key
      if (age >= 0 && age < 20) {
        key = '0-20岁'
      } else if (age >= 20 && age < 40) {
        key = '20-40岁'
      } else if (age >= 40 && age < 60) {
        key = '40-60岁'
      } else {
        key = '60岁以上'
      }

      return key
    }

    // 按年龄分组
    await knex(TABLE_PERSON)
      .select(knex.raw(counts.join(',')))
      .groupBy('age')
      .then(res => {
        return res.map(item => {
          total += item.total
          const ageKey = getAgeKey(item.age)
          ageGroup[ageKey] += item.total

          return item
        })
      })

    await this.setData({
      trackScope: 'all',
      trackName: 'person_age_group',
      trackTitle: '人员年龄段',
      trackData: Object.keys(ageGroup).map(key => {
        return {
          label: key,
          value: ageGroup[key],
        }
      }),
      total,
    })

    return ageGroup
  }

  /**
   * 乡镇特殊人员
   */
  async townSpecialPerson() {
    const { knex } = this.app
    const { TABLE_PERSON } = this.config.constant
    const { helper } = this.ctx

    const data = town.map(item => {
      return {
        label: item,
      }
    })

    await helper.queue(data, async (item, index) => {
      const townName = item.label

      await knex(TABLE_PERSON)
        .select(
          knex.raw(
            [
              `areajd as label`,
              `count(djlr = '是' or null) AS djlr`,
              `count(jszz = '是' or null) AS jszz`,
              `count(cqwc = '是' or null) AS cqwc`,
              `count(cjr = '是' or null) AS cjr`,
              `count(jcbhz = '是' or null) AS jcbhz`,
              `count(xytxhz = '是' or null) AS xytxhz`,
            ].join(',')
          )
        )
        .where('areajd', townName)
        .then(res => Object.assign(item, res[0]))
    })

    // const counts = [
    //   `areajd as label`,
    //   `count(djlr = '是' or null) AS djlr`,
    //   `count(jszz = '是' or null) AS jszz`,
    //   `count(cqwc = '是' or null) AS cqwc`,
    //   `count(cjr = '是' or null) AS cjr`,
    //   `count(jcbhz = '是' or null) AS jcbhz`,
    //   `count(xytxhz = '是' or null) AS xytxhz`,
    // ]
    // const data = await knex(TABLE_PERSON)
    //   .select(knex.raw(counts.join(',')))
    //   .groupBy('label')
    //   .then(res => {
    //     return res.map(item => {
    //       let total = 0

    //       Object.keys(item).map(key => {
    //         if (key == 'label') return
    //         total += item[key]
    //       })

    //       item.total = total

    //       return item
    //     })
    //   })

    await this.setData({
      trackScope: 'all',
      trackName: 'town_special_person',
      trackTitle: '乡镇特殊人群',
      trackData: data,
    })

    return data
  }

  /**
   * 人口分类数据
   */
  async personCategory() {
    const { moss, knex } = this.app
    const { prefix } = this.config
    const { TABLE_PERSON } = this.config.constant

    const tsrq = await knex(TABLE_PERSON)
      .orWhere('djlr', '是')
      .orWhere('jszz', '是')
      .orWhere('cqwc', '是')
      .orWhere('cjr', '是')
      .orWhere('jcbhz', '是')
      .orWhere('xytxhz', '是')
      .count()
      .then(res => {
        return res[0]['count(*)']
      })

    const counts = [
      `count(sfczrq = '是' or null) AS cz`,
      `count(sfzh = '是' or null) AS zh`,
      `count(outtype = '在外务工' or null) AS wcwg`,
      `count(outtype = '在外经商' or null) AS wcjs`,
      `count(outtype = '在外上学' or null) AS wcsx`,
    ]
    const data = await knex(TABLE_PERSON)
      .select(knex.raw(counts.join(',')))
      .then(res => {
        return {
          ...res[0],
          tsrq,
        }
      })

    if (prefix == 'xhx') {
      await knex(TABLE_PERSON)
        .count()
        .then(res => {
          data.cz = res[0]['count(*)']
        })
    }

    await this.setData({
      trackScope: 'all',
      trackName: 'person_type',
      trackTitle: '人口分类数据',
      trackData: data,
    })

    return data
  }

  /**
   * 返乡外来人口趋势
   * @param  date 日期
   * @param  scope 范围 week/month
   * @returns
   */
  async enterTrendByScope(...args) {
    const { knex } = this.app
    const { TABLE_PERSON_BACK_HOME } = this.config.constant
    const { helper } = this.ctx

    const { startTime, endTime, label, scope } = helper.getDateOptions(...args)

    const total = await knex(TABLE_PERSON_BACK_HOME)
      .where('entry_time', '>=', startTime)
      .where('entry_time', '<=', endTime)
      .count()
      .then(res => {
        return res[0]['count(*)']
      })

    await this.setData({
      trackScope: scope,
      trackName: scope + '_person_enter_trend',
      trackTitle: label + '返乡外来人口趋势',
      trackDate: startTime,
      total,
    })

    return total
  }

  // 同步返乡人员数据
  async syncFxyr() {
    const { ctx } = this
    const { TABLE_PERSON_BACK_HOME } = this.config.constant
    const count = await ctx.model.Fxry.count()

    const handle = async data => {
      const insertData = data.map(item => {
        return {
          targetId: item.id.toString(),
          idCard: item.id_number,
          entryTime: item.entry_time,
          name: item.name,
          town: item.to_county_code,
        }
      })

      await ctx.model.Fxry.insertMany(insertData)
    }

    const getDataByIndex = async (startIndex, pageSize = 1000) => {
      const { knex } = this.app

      const data = await knex(TABLE_PERSON_BACK_HOME).offset(startIndex).limit(pageSize)

      await handle(data)

      if (data.length == pageSize) await getDataByIndex(startIndex + pageSize, pageSize)
    }

    await getDataByIndex(count)
  }

  // 获取返乡人员扫码数据
  async getFxryScanData() {
    const { ctx } = this
    const { moss, knex } = this.app
    const { helper } = ctx
    const { dict } = this.config
    const { TABLE_SCAN_RECORD } = this.config.constant

    const handle = async data => {
      const idCards = data.map(item => item.idCard)
      const scanData = await knex(TABLE_SCAN_RECORD).whereIn('card_no', idCards)

      await helper.queue(scanData, async (item, index) => {
        await ctx.model.Scan.findOneAndUpdate(
          {
            targetId: item.id,
          },
          {
            targetId: item.id,
            placeName: item.place_name,
            placeTypeId: item.cstype,
            scanTime: item.scancode_date,
            fxry: true,
          },
          {
            upsert: true,
          }
        )
      })
    }

    const getData = async (page = 1, pageSize = 10) => {
      const data = await ctx.model.Fxry.find({
        // 3天内
        entryTime: {
          $gte: dayjs().add(-3, 'day').format('YYYY-MM-DD HH:mm:ss'),
        },
      })
        .skip((page - 1) * pageSize)
        .limit(pageSize)

      await handle(data)

      if (data.length == pageSize) return await getData(page + 1, pageSize)
    }

    await getData()
  }

  // 获取返乡人员核酸记录
  async getFxryHsData() {
    const { ctx } = this
    const { moss, knex } = this.app
    const { helper } = ctx
    const { dict } = this.config
    const { TABLE_HS_RESULT } = this.config.constant

    const handle = async data => {
      const idCards = data.map(item => item.idCard)
      const hsData = await knex(TABLE_HS_RESULT).whereIn('yw_id_card', idCards)

      await helper.queue(hsData, async (hsItem, index) => {
        const user = data.find(item => hsItem.yw_id_card == item.idCard)

        await ctx.model.Hs.findOneAndUpdate(
          {
            targetId: hsItem.id,
          },
          {
            targetId: hsItem.id,
            checkTime: hsItem.YW_REPORT_TIME,
            idCard: hsItem.yw_id_card,
            fxry: true, // 返乡人员
            town: user.town,
          },
          {
            upsert: true,
          }
        )
      })
    }

    const getData = async (page = 1, pageSize = 100) => {
      const data = await ctx.model.Fxry.find({
        // 3天内
        entryTime: {
          $gte: dayjs().add(-3, 'day').format('YYYY-MM-DD HH:mm:ss'),
        },
      })
        .skip((page - 1) * pageSize)
        .limit(pageSize)

      await handle(data)

      if (data.length == pageSize) return await getData(page + 1, pageSize)
    }

    await getData()
  }

  // 同步重点人员数据
  // async syncZdry() {
  //   const { ctx } = this
  //   const { TABLE_ZDRY } = this.config.constant
  //   const count = await ctx.model.Zdry.count()

  //   const handle = async data => {
  //     const insertData = data.map(item => {
  //       return {
  //         targetId: item.id.toString(),
  //         idCard: item.person_id_card,
  //         createTime: item.create_time,
  //         name: item.person_name,
  //         town: item.areajd,
  //       }
  //     })

  //     await ctx.model.Zdry.insertMany(insertData)
  //   }

  //   const getDataByIndex = async (startIndex, pageSize = 100) => {
  //     const { knex } = this.app

  //     const data = await knex(TABLE_ZDRY).offset(startIndex).limit(pageSize)

  //     await handle(data)

  //     if (data.length == pageSize) await getDataByIndex(startIndex + pageSize, pageSize)
  //   }

  //   await getDataByIndex(count)
  // }

  // 获取重点人员核酸记录
  async getZdryHsData(beforeDay = 7) {
    const { ctx } = this
    const { knex } = this.app
    const { helper } = ctx
    const { TABLE_HS_RESULT, TABLE_ZDRY } = this.config.constant

    const handle = async users => {
      const idCards = users.map(item => item.person_id_card)
      const hsData = await knex(TABLE_HS_RESULT).whereIn('yw_id_card', idCards)

      // await helper.queue(hsData, async (hsItem, index) => {
      //   const user = users.find(item => hsItem.yw_id_card == item.person_id_card)

      //   await ctx.model.Hs.findOneAndUpdate(
      //     {
      //       targetId: hsItem.id,
      //     },
      //     {
      //       targetId: hsItem.id,
      //       checkTime: hsItem.YW_REPORT_TIME,
      //       idCard: hsItem.yw_id_card,
      //       zdry: true, // 返乡人员
      //       town: user.areajd,
      //     },
      //     {
      //       upsert: true,
      //     }
      //   )
      // })
    }

    const getData = async (page = 1, pageSize = 100) => {
      const data = await knex(TABLE_ZDRY)
        .where(
          'create_time',
          '>=',
          dayjs()
            .add(0 - beforeDay, 'day')
            .format('YYYY-MM-DD HH:mm:ss')
        )
        .offset((page - 1) * pageSize)
        .limit(pageSize)

      await handle(data)

      if (data.length == pageSize) return await getData(page + 1, pageSize)
    }

    await getData()
  }

  /**
   * 返乡人员核酸预警
   * @param  date 日期
   * @param  scope 范围 week/month
   * @returns
   */
  async hsyj(...args) {
    const { knex } = this.app
    const { helper } = this.ctx
    const { TABLE_PERSON_BACK_HOME, TABLE_HS_RESULT } = this.config.constant

    const data = town.map(item => {
      return {
        address: item,
      }
    })

    await helper.queue(data, async (item, index) => {
      const townName = item.address

      item.personNum = await knex(TABLE_PERSON_BACK_HOME)
        .where('to_county_code', townName)
        .count()
        .then(res => res[0]['count(*)'])

      const count = await knex
        .select(knex.raw([`count(*) AS total`].join(',')))
        .from(function () {
          this.select(knex.raw([`count(*) AS total`].join(',')))
            .from(function () {
              this.table(TABLE_HS_RESULT)
                .join(
                  TABLE_PERSON_BACK_HOME,
                  `${TABLE_HS_RESULT}.yw_id_card`,
                  '=',
                  `${TABLE_PERSON_BACK_HOME}.id_number`
                )
                .select(`${TABLE_HS_RESULT}.*`, `${TABLE_PERSON_BACK_HOME}.to_county_code`)
                .where('YW_REPORT_TIME', '>=', dayjs().add(-48, 'hour').format('YYYY-MM-DD HH:mm:ss'))
                .where('YW_REPORT_TIME', '<=', dayjs().format('YYYY-MM-DD HH:mm:ss'))
                .where(`${TABLE_PERSON_BACK_HOME}.to_county_code`, townName)
                .as('hsResult')
            })
            .groupBy(`hsResult.yw_id_card`)
            .as('hsResultGroupBy')
        })
        .then(res => (res.length ? res[0].total : 0))

      // 48小时内未做核酸
      item.noCheck = item.personNum - count
    })

    // const data = await this.ctx.model.Fxry.aggregate([
    //   {
    //     $group: {
    //       _id: '$town',
    //       personNum: {
    //         $sum: 1,
    //       },
    //     },
    //   },
    //   {
    //     $project: {
    //       _id: 0,
    //       address: '$_id',
    //       personNum: 1,
    //     },
    //   },
    //   {
    //     $sort: {
    //       personNum: -1,
    //     },
    //   },
    // ])

    // await this.ctx.helper.queue(data, async item => {
    //   // 48小时内已做核酸
    //   const count = await this.ctx.model.Hs.count({
    //     town: item.address,
    //     fxry: true,
    //     checkTime: {
    //       $gte: dayjs().add(-48, 'hour').format('YYYY-MM-DD HH:mm:ss'),
    //       $lte: dayjs().format('YYYY-MM-DD HH:mm:ss'),
    //     },
    //   })

    //   // 48小时内未做核酸
    //   item.noCheck = item.personNum - count
    // })

    await this.setData({
      trackScope: 'all',
      trackName: 'hsyj',
      trackTitle: '返乡人员核酸预警',
      trackData: data,
    })

    return data
  }

  /**
   * 返乡人员场所扫码
   * @param  date 日期
   * @param  scope 范围 week/month
   * @returns
   */
  async fxryPlaceScanByScope(...args) {
    const { dict } = this.config
    const { helper } = this.ctx
    const { startTime, endTime, label, scope } = helper.getDateOptions(...args)

    const data = await this.ctx.model.Scan.aggregate([
      {
        $match: {
          fxry: true,
          scanTime: {
            $gte: new Date(startTime),
            $lte: new Date(endTime),
          },
        },
      },
      {
        $group: {
          _id: '$placeName',
          scanNum: {
            $sum: 1,
          },
          placeTypeId: { $last: '$placeTypeId' },
        },
      },
      {
        $project: {
          _id: 0,
          placeName: '$_id',
          placeTypeId: 1,
          scanNum: 1,
          placeTypeId: 1,
        },
      },
      {
        $sort: {
          scanNum: -1,
        },
      },
    ]).then(res => {
      return res.map(item => {
        if (item.placeTypeId < 10) item.placeTypeId = '0' + item.placeTypeId

        return {
          ...item,
          placeType: dict.placeTypeValues[item.placeTypeId] || `${item.placeTypeId}未知`,
        }
      })
    })

    await this.setData({
      trackScope: scope,
      trackName: scope + '_hsyj_place_scan',
      trackTitle: label + '返乡人员场所扫码',
      trackData: data,
    })

    return data
  }

  // 获取日期选项
  getDateOptions(...args) {
    return this.ctx.helper.getDateOptions(...args)
  }

  /**
   * 返乡人员居住地
   * @param  date 日期
   * @param  scope 范围 week/month
   * @returns
   */
  async fxryJzdByScope(...args) {
    const { startTime, endTime, label, scope } = this.ctx.helper.getDateOptions(...args)

    const data = await this.ctx.model.Fxry.aggregate([
      {
        $match: {
          entryTime: {
            $gte: new Date(startTime),
            $lte: new Date(endTime),
          },
        },
      },
      {
        $group: {
          _id: '$town',
          personNum: {
            $sum: 1,
          },
        },
      },
      {
        $project: {
          _id: 0,
          address: '$_id',
          personNum: 1,
        },
      },
      {
        $sort: {
          personNum: -1,
        },
      },
    ]).then(res => {
      return res.map(item => {
        return {
          ...item,
          jjgl: 0,
          jzgl: 0,
        }
      })
    })

    await this.setData({
      trackScope: scope,
      trackName: scope + '_hsyj_home_address',
      trackTitle: label + '返乡人员居住地',
      trackDate: startTime,
      trackData: data,
    })

    return data
  }

  /**
   * 风险预警
   */
  async fxyj() {
    const { ctx } = this
    const { knex } = this.app
    const { helper } = ctx
    const { TABLE_HS_RESULT, TABLE_ZDRY } = this.config.constant

    const data = town.map(item => {
      return {
        name: item,
      }
    })

    await helper.queue(data, async (item, index) => {
      const townName = item.name

      // 总数人数
      const zrs = await this.service.person.reportTotalByTown(townName)
      // 返乡人员核酸人数
      const hsrs = await this.service.hs.hxryHsTotalByTown(townName)

      item.hsrs = hsrs

      // 重点人员数量
      const zdry = await knex(TABLE_ZDRY)
        .where('areajd', townName)
        .count()
        .then(res => res[0]['count(*)'])

      // 重点人员3天核酸数
      const zdry3ths = await this.service.hs.getZdryHsCount(townName, 3)
      const zdry5ths = await this.service.hs.getZdryHsCount(townName, 5)

      // 混采记录
      const hcjl = await this.service.hs.getJjglHcCount(townName)

      // 扫码记录
      const smjl = await this.service.scan.getJjglScanCount(townName)

      // 未做核酸人数= 总数人数 - 已做核酸数
      item.bbwzhs = zrs - hsrs

      item.zdry3twzhs = zdry > zdry3ths ? zdry - zdry3ths : 0
      item.zdry5twzhs = zdry > zdry5ths ? zdry - zdry5ths : 0
      item.frmz7twzhs = 0
      item.jzglhcjl = hcjl || 0
      item.jzglsmjl = smjl || 0
    })

    await this.setData({
      trackScope: 'all',
      trackName: 'fxyj',
      trackTitle: '风险预警',
      trackData: data,
    })

    return data
  }

  // 返乡报备信息
  async reportByScope(...args) {
    const { ctx } = this
    const { moss, knex } = this.app
    const { dict } = this.config
    const { TABLE_PERSON_BACK_HOME, TABLE_HS_RESULT } = this.config.constant

    const { startTime, endTime, label, scope } = this.ctx.helper.getDateOptions(...args)

    // 返乡总人数
    const counts = [
      `count(*) AS zrs`, // 返乡总人数
      `count(entry_way = 1 or null) AS hc`, // 火车
      `count(entry_way = 3 or null) AS ctqc`, // 长途汽车
      `count(entry_way = 4 or null) AS zj`, // 自驾
      `count(visited_adventure_area = 1 or null) AS gfx`, // 高风险
    ]

    const data = await knex(TABLE_PERSON_BACK_HOME)
      .select(knex.raw(counts.join(',')))
      .where('entry_time', '>=', startTime)
      .where('entry_time', '<=', endTime)
      .then(res => {
        return res[0] || {}
      })

    // 核酸人数
    // data.hsrs = await ctx.model.Hs.count({
    //   fxry: true,
    //   checkTime: {
    //     $gte: startTime,
    //     $lte: endTime,
    //   },
    // })
    data.hsrs = await knex
      .select(knex.raw([`count(*) AS total`].join(',')))
      .from(function () {
        this.select(knex.raw([`count(*) AS total`].join(',')))
          .from(function () {
            this.table(TABLE_HS_RESULT)
              .join(
                TABLE_PERSON_BACK_HOME,
                `${TABLE_HS_RESULT}.yw_id_card`,
                '=',
                `${TABLE_PERSON_BACK_HOME}.id_number`
              )
              .select(`${TABLE_HS_RESULT}.*`, `${TABLE_PERSON_BACK_HOME}.to_county_code`)
              .where(`${TABLE_PERSON_BACK_HOME}.entry_time`, '>=', startTime)
              .where(`${TABLE_PERSON_BACK_HOME}.entry_time`, '<=', endTime)
              .as('hsResult')
          })
          .groupBy(`hsResult.yw_id_card`)
          .as('hsResultGroupBy')
      })
      .then(res => (res.length ? res[0].total : 0))

    data.hsjcl = data.zrs ? Number(((data.hsrs / data.zrs) * 100).toFixed(2)) : 0

    await this.setData({
      trackScope: scope,
      trackName: scope + '_report_info',
      trackTitle: label + '返乡报备信息',
      trackDate: startTime,
      trackData: data,
    })

    return data
  }

  // 态势总览信息
  async homeInfo(...args) {
    const { ctx } = this
    const { moss, knex } = this.app
    const { prefix } = this.config
    const { TABLE_PERSON, TABLE_PERSON_BACK_HOME, TABLE_OUT } = this.config.constant

    const data = {}

    await Promise.all([
      // 常住人口和非常住
      (async () => {
        if (prefix == 'xhx') {
          data.cz = await knex(TABLE_PERSON)
            .count()
            .then(res => res[0]['count(*)'])
          data.fcz = await knex(TABLE_OUT)
            .count()
            .then(res => res[0]['count(*)'])

          // data.fcz = await knex(TABLE_PERSON_BACK_HOME)
          //   .count()
          //   .then(res => res[0]['count(*)'])
        } else {
          await knex(TABLE_PERSON)
            .select(
              knex.raw(
                [
                  `count(sfczrq = '是' or null) AS cz`, // 常住人口
                  `count(sfczrq = '否' or null) AS fcz`, // 非常住人口
                ].join(',')
              )
            )
            .then(res => {
              data.cz = res[0].cz
              data.fcz = res[0].fcz
            })
        }
      })(),

      // 街道/乡镇
      knex
        .raw(`select count(*) from (SELECT areajd FROM ${TABLE_PERSON} GROUP BY areajd ) as t`)
        .then(res => (data.jd = res[0][0]['count(*)'])),
      // 现住居委会/村委会
      knex
        .raw(`select count(*) from (SELECT areajwh FROM ${TABLE_PERSON} GROUP BY areajwh ) as t`)
        .then(res => (data.jhw = res[0][0]['count(*)'])),
      // 小区/自然村
      knex
        .raw(`select count(*) from (SELECT xqmc FROM ${TABLE_PERSON} GROUP BY xqmc ) as t`)
        .then(res => (data.xq = res[0][0]['count(*)'])),
    ])

    await this.setData({
      trackScope: 'all',
      trackName: 'home_info',
      trackTitle: '态势总览信息',
      trackData: data,
    })

    return data
  }

  // 场所码信息
  async codeInfo(...args) {
    const { ctx } = this
    const { moss, knex } = this.app
    const { prefix } = this.config
    const { helper } = ctx
    const { TABLE_PERSON, TABLE_PERSON_BACK_HOME, TABLE_SCAN_RECORD, TABLE_PLACE_CODE, TABLE_OUT } =
      this.config.constant

    const { startTime, endTime, label, scope } = this.ctx.helper.getDateOptions(...args)

    const data = {
      baseInfo: [
        { placeCategory: '商超', placeNum: 0, scanNum: 0 },
        { placeCategory: '医院', placeNum: 0, scanNum: 0 },
        { placeCategory: '学校', placeNum: 0, scanNum: 0 },
        { placeCategory: '公交', placeNum: 0, scanNum: 0 },
      ],
    }
    await Promise.all([
      // 常住人口和非常住
      (async () => {
        if (prefix == 'xhx') {
          data.cz = await knex(TABLE_PERSON)
            .count()
            .then(res => res[0]['count(*)'])

          data.fcz = await knex(TABLE_OUT)
            .count()
            .then(res => res[0]['count(*)'])

          // data.fcz = await knex(TABLE_PERSON_BACK_HOME)
          //   .count()
          //   .then(res => res[0]['count(*)'])
        } else {
          await knex(TABLE_PERSON)
            .select(
              knex.raw(
                [
                  `count(sfczrq = '是' or null) AS cz`, // 常住人口
                  `count(sfczrq = '否' or null) AS fcz`, // 非常住人口
                ].join(',')
              )
            )
            .then(res => {
              data.cz = res[0].cz
              data.fcz = res[0].fcz
            })
        }
      })(),

      // knex(TABLE_PERSON)
      //   .select(
      //     knex.raw(
      //       [
      //         `count(sfczrq = '是' or null) AS cz`, // 常住人口
      //         `count(sfczrq = '否' or null) AS fcz`, // 非常住人口
      //       ].join(',')
      //     )
      //   )
      //   .then(res => {
      //     data.cz = res[0].cz
      //     data.fcz = res[0].fcz
      //   }),

      knex(TABLE_SCAN_RECORD)
        .where('scancode_date', '>=', startTime)
        .where('scancode_date', '<=', endTime)
        .count()
        .then(res => {
          data.scanNum = res[0]['count(*)']
        }),
    ])

    await helper.queue(data.baseInfo, async item => {
      await knex(TABLE_SCAN_RECORD)
        // .where('scancode_date', '>=', startTime)
        // .where('scancode_date', '<=', endTime)
        .where({
          cstype: {
            商超: 6,
            医院: 8,
            学校: 15,
            公交: 4,
          }[item.placeCategory],
        })
        .count()
        .then(res => {
          item.scanNum = res[0]['count(*)']
        })
      await knex(TABLE_PLACE_CODE)
        // .where('scancode_date', '>=', startTime)
        // .where('scancode_date', '<=', endTime)
        .where({
          unit_type: {
            商超: '06',
            医院: '08',
            学校: '15',
            公交: '04',
          }[item.placeCategory],
        })
        .count()
        .then(res => {
          item.placeNum = res[0]['count(*)']
        })
    })

    await this.setData({
      trackScope: 'all',
      trackName: 'code_info',
      trackTitle: '场所码信息',
      trackData: data,
    })

    return data
  }

  // 核酸检测信息
  async hsInfo() {
    const { ctx } = this
    const { moss, knex } = this.app
    const { dict } = this.config
    const { helper } = ctx
    const { TABLE_HS_RESULT, TABLE_PERSON, TABLE_HS_COLLECT } = this.config.constant

    const { startTime, endTime } = this.ctx.helper.getDateOptions()

    const data = {}

    await Promise.all([
      // 应检
      knex(TABLE_PERSON)
        .count()
        .then(res => {
          data.yingjian = res[0]['count(*)']
        }),
      // knex(TABLE_HS_COLLECT)
      //   .where('col_time', '>=', startTime)
      //   .where('col_time', '<=', endTime)
      //   .count()
      //   .then(res => {
      //     data.yingjian = res[0]['count(*)']
      //   }),
      // 已检验
      knex(TABLE_HS_RESULT)
        .where('YW_REPORT_TIME', '>=', startTime)
        .where('YW_REPORT_TIME', '<=', endTime)
        .count()
        .then(res => {
          data.yijian = res[0]['count(*)']
        }),
    ])

    data.weijian = data.yingjian - data.yijian
    data.jcl = data.yijian ? (data.yijian / data.yingjian) * 100 : 0

    await this.setData({
      trackScope: 'all',
      trackName: 'hs_info',
      trackTitle: '核酸信息',
      trackData: data,
    })

    return data
  }

  // 返乡报备信息
  async reportInfo() {
    const { ctx } = this
    const { knex } = this.app
    const { helper } = ctx
    const { TABLE_POSITIVE, TABLE_PERSON_BACK_HOME } = this.config.constant
    const { startTime, endTime } = this.ctx.helper.getDateOptions()

    const data = town.map(item => {
      return {
        total: 0, // 累计
        label: item, // 乡镇
        new: 0, // 当日新增
        jzgl: 0, // 集中隔离
        jjgl: 0, // 居家隔离
      }
    })

    await helper.queue(data, async (item, index) => {
      const townName = item.label

      // 累计
      item.total = await knex(TABLE_PERSON_BACK_HOME)
        .where('to_county_code', townName)
        .count()
        .then(res => res[0]['count(*)'])

      // 当日新增
      item.new = await knex(TABLE_PERSON_BACK_HOME)
        .where('to_county_code', townName)
        .where('entry_time', '>=', dayjs().format('YYYY-MM-DD 00:00:00'))
        .where('entry_time', '<=', dayjs().format('YYYY-MM-DD 23:59:59'))
        .count()
        .then(res => res[0]['count(*)'])

      await knex(TABLE_POSITIVE)
        .select(
          knex.raw([`count(sfjzgl = '1' or null) AS jzgl`, `count(sfjzgl = '2' or null) AS jjgl`].join(','))
        )
        .where('areajd', townName)
        .then(res => {
          for (const key in res[0]) {
            item[key] = res[0][key]
          }
        })
    })

    await this.setData({
      trackScope: 'all',
      trackName: 'report',
      trackTitle: '返乡报备信息',
      trackData: data,
    })

    return data
  }

  // 获取数据
  async getData(trackName) {
    const { ctx } = this
    const data = await ctx.model.TrackData.findOne({ trackName })
      .sort({ trackDate: 'DESC' })
      .sort({ _id: 'DESC' })

    if (!data) throw '暂无数据'

    return data.trackData
  }

  // 获取数据
  async getDataList(trackName) {
    const { ctx } = this
    const data = await ctx.model.TrackData.find({ trackName }).sort({ _id: 'DESC' })

    if (!data) throw '暂无数据'

    return data
  }
}

module.exports = ScreenService
